package top.wuyang.study.java24.dao;

import top.wuyang.study.java24.entity.Result;
import top.wuyang.study.java24.entity.TbSoftwareType;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class TbSoftwareTypeDAO {

    public static final String INSERT = "insert into tb_software_type(type_name, description) value (?, ?)";
    public static final String QUERY_BY_NAME = "select * from tb_software_type where type_name = ?";
    public static final String QUERY = "select * from tb_software_type";
    public static final String QUERY_BY_ID = "select * from tb_software_type where id = ?";
    public static final String QUERY_COUNT_BY_ID = "select count(*) from tb_software where type_id = ?";
    public static final String DELETE = "delete from tb_software_type where id = ?";
    public static final String UPDATE = "update tb_software_type set type_name=?,description=? where id=?";


    // 添加
    public static int insert(TbSoftwareType softwareType) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(INSERT);
        ps.setString(1, softwareType.getType_name());
        ps.setString(2, softwareType.getDescription());
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }

    // 通过名称查询--防止重名
    public static TbSoftwareType queryByName(String name) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY_BY_NAME);
        ps.setString(1, name);
        TbSoftwareType softwareType = null;
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            softwareType = new TbSoftwareType();
            softwareType.setId(rs.getInt("id"));
            softwareType.setType_name(rs.getString("type_name"));
            softwareType.setDescription(rs.getString("description"));
        }
        conn.close();
        return softwareType;
    }

    // 查询所有部门
    public static List<TbSoftwareType> query() throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY);
        List<TbSoftwareType> list = new ArrayList<>();
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            TbSoftwareType softwareType = new TbSoftwareType();
            softwareType.setId(rs.getInt("id"));
            softwareType.setType_name(rs.getString("type_name"));
            softwareType.setDescription(rs.getString("description"));

            list.add(softwareType);
        }
        conn.close();
        return list;
    }

    public static int queryCountById(Integer id) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY_COUNT_BY_ID);
        ps.setInt(1, id);
        int count = 0;
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            count = rs.getInt(1);
        }
        conn.close();
        return count;
    }

    public static int delete(Integer id) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(DELETE);
        ps.setInt(1, id);
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }

    public static TbSoftwareType queryById(Integer id) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY_BY_ID);
        ps.setInt(1, id);
        TbSoftwareType softwareType = null;
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            softwareType = new TbSoftwareType();
            softwareType.setId(rs.getInt("id"));
            softwareType.setType_name(rs.getString("type_name"));
            softwareType.setDescription(rs.getString("description"));
        }
        conn.close();
        return softwareType;
    }

    public static int update(TbSoftwareType softwareType) throws Exception{
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(UPDATE);
        ps.setString(1, softwareType.getType_name());
        ps.setString(2, softwareType.getDescription());
        ps.setInt(3, softwareType.getId());
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }

}
